<?php

/**
	* Simple MySQL Table representation
	*
	* @author ADi
	*/
class MysqlTable {
	private $name;
	private $primaryKeyColumn;
	private $primaryKeyValue = null;
	private $primaryKeyIsDAOId = false;
	private $columns = array();
	/**
	 * parent table if exists
	 * @var MysqlTable
	 */
	private $parentTable = null;

	/**
	 * constructor
	 * @param string $name table name
	 * @param array $columns column list
	 * @param string $primaryKey primary key field name
	 * @param bool $primaryKeyIsDAOId sets primary key field as DAO ID
	 */
	public function __construct($name, Array $columns, $primaryKey, $primaryKeyIsDAOId = true) {
		$this->name = $name;
		$this->setColumns($columns);
		$this->primaryKeyColumn = $primaryKey;
		$this->primaryKeyIsDAOId = $primaryKeyIsDAOId;
		if(isset($this->columns[$primaryKey])) {
			unset($this->columns[$primaryKey]);
		}
	}

	/**
	 * get table name
	 * @return string
	 */
	public function getName() {
		return $this->name;
	}

	private function setColumns(Array $columnNames) {
		foreach	($columnNames as $name) {
			$this->columns[$name] = false;
		}
	}

	private function getColumnValue($colName, Array $values) {
		return ( isset($values[$colName]) ? $values[$colName] : ( isset($values[$this->name.'.'.$colName]) ? $values[$this->name.'.'.$colName] : null ) );
	}

	private function getForeignKeyValuePair(Array $fields) {
		foreach($this->columns as $colName => $colReference) {
			if(!empty($colReference) && !empty($fields[$colReference])) {
				return array( 'column' => $colName, 'value' => $fields[$colReference] );
			}
		}
		throw new 	TinyMVCException("MysqlTable - Unable to determine parent table referencing key-value pair");
	}

	/**
	 * add parent table
	 * @param MysqlTable $table parent table
	 * @param string $parentColumn referencing parent column name
	 * @param string $childColumn child column name
	 * @return MysqlTable
	 */
	public function setParentTable(MysqlTable $table, $parentColumn, $childColumn) {
		$this->parentTable = $table;
		$this->columns[$childColumn] = $parentColumn;
		return $this;
	}

	/**
	 * Insert row(s) into mysql table(s)
	 * @param MysqlDriver $db
	 * @param array $fields
	 * @return int DAO Id
	 */
	public function runInsertStatement(MysqlDriver $db, Array &$fields) {
		$DAOId = 0;

		if($this->parentTable instanceof MysqlTable) {
			$insertId = $this->parentTable->runInsertStatement($db, $fields);
			if(!empty($insertId)) {
				$DAOId = $insertId;
			}
		}

		$columns = '';
		$values = '';
		foreach($this->columns as $colName => $referenceColumn ) {
			if(array_key_exists($colName, $fields) || array_key_exists(($this->name.'.'.$colName), $fields) || !empty($referenceColumn)) {
				$columns .= ( $columns ? ',' : '' ) . ( $colName  );
				$value = $this->getColumnValue(( !empty($referenceColumn) ? $referenceColumn : $colName ), $fields);
				$values .= ( $values ? ',' : '' ) . ( is_null($value) ? "NULL" : "'" . addslashes($value) . "'" );
			}
		}

		$db->query("INSERT INTO {$this->name} (". $columns .") VALUES (". $values . ")");
		$row = $db->query("SELECT LAST_INSERT_ID() AS id")->getNextRow();

		if(!empty($row['id'])) {
			$this->primaryKeyValue = $row['id'];
			$fields[$this->primaryKeyColumn] = $this->primaryKeyValue;
			if($this->primaryKeyIsDAOId) {
				$DAOId = $this->primaryKeyValue;
			}
		}
		else {
			throw new MysqlException("MysqlTable - INSERT Failed: ID has not been returned ({$this->name}.{$this->primaryKeyColumn})");
		}
		return  $DAOId;
	}

	/**
	 * Fetch data from mysql table(s) (no JOINs here, it's evil!  - simple SELECTs only! :)
	 *
	 * @param MysqlDriver $db
	 * @param int $DAOId
	 * @param array $fields
	 * @param bool $forUpdate add FOR UPDATE statement
	 * @return bool returns true when row with DAO ID was fetched
	 */
	public function runSelectStatement(MysqlDriver $db, $DAOId, Array &$fields, $forUpdate = false) {
		$parentRunResult = false;
		if($this->parentTable instanceof MysqlTable) {
			$parentRunResult = $this->parentTable->runSelectStatement($db, $DAOId, $fields, $forUpdate);
		}

		// determine primary key-value pair for this table
		if($this->primaryKeyIsDAOId) {
			$primaryKeyColumn = $this->primaryKeyColumn;
			$primaryKeyValue = $DAOId;
		}
		elseif(isset($fields[$this->primaryKeyColumn])) {
			$primaryKeyColumn = $this->primaryKeyColumn;
			$primaryKeyValue = $fields[$this->primaryKeyColumn];
		}
		else {
			//we don't know primary key-value yet, use parent reference
			$foreignKeyValuePair = $this->getForeignKeyValuePair($fields);
			$primaryKeyColumn = $foreignKeyValuePair['column'];
			$primaryKeyValue = $foreignKeyValuePair['value'];
		}

		$row = $db->query("SELECT * FROM {$this->name} WHERE {$primaryKeyColumn}='{$primaryKeyValue}'" . ( $forUpdate ? " FOR UPDATE" : "" ))->getNextRow();

		if(!empty($row)) {
			foreach ($row as $fieldName => $value) {
				if(!is_int($fieldName)) {
					$fields[$fieldName] = $value;
				}
			}

			$this->primaryKeyValue = $row[$this->primaryKeyColumn];
			$runResult = $this->primaryKeyIsDAOId ? !empty($this->primaryKeyValue) : false;
		}
		else {
			throw new MysqlException("MysqlTable - SELECT Failed: no results for given ID ({$this->name}.{$primaryKeyColumn}={$primaryKeyValue})");
		}
		return ($parentRunResult || $runResult );
	}

	/**
	 * Update row(s) in mysql table(s)
	 * @param MysqlDriver $db
	 * @param array $fields
	 */
	public function runUpdateStatement(MysqlDriver $db, Array &$fields) {
		if($this->parentTable instanceof MysqlTable) {
			$this->parentTable->runUpdateStatement($db, $fields);
		}

		$colValues = '';
		foreach(array_keys($this->columns) as $colName) {
			$colValues .= ( $colValues ? ',' : 'SET ' ) . $colName . ( is_null($fields[$colName])  ? "=NULL" : "='" . addslashes($fields[$colName]) . "'" );
		}

		$db->query("UPDATE {$this->name} {$colValues} WHERE {$this->primaryKeyColumn}='{$this->primaryKeyValue}'");
		return true;
	}

	/**
	 * Delete row(s) from mysql table(s)
	 * @param MysqlDriver $db
	 */
	public function runDeleteStatement(MysqlDriver $db) {
		if($this->parentTable instanceof MysqlTable) {
			$this->parentTable->runDeleteStatement($db);
		}

		$db->query("DELETE FROM {$this->name} WHERE {$this->primaryKeyColumn}='{$this->primaryKeyValue}'");
		return true;
	}

}
